DROP TABLE VOOS CASCADE CONSTRAINTS
;
CREATE TABLE VOOS
(
	CODIGO   VARCHAR2(10) NOT NULL,
	ORIGEM   VARCHAR2(50) NOT NULL,
	DESTINO  VARCHAR(50) NOT NULL,
	DATA     DATE NULL
)
;


ALTER TABLE VOOS ADD CONSTRAINT PK_VOOS 
	PRIMARY KEY (CODIGO) 
 USING INDEX 
;

ALTER TABLE VOOS ADD CONSTRAINT FK_ORIGEM 
	FOREIGN KEY (ORIGEM) REFERENCES AEROPORTOS (CODIGO)
;

ALTER TABLE VOOS ADD CONSTRAINT FK_VOOS_AEROPORTOS 
	FOREIGN KEY (DESTINO) REFERENCES AEROPORTOS (CODIGO)
;


CREATE SEQUENCE  "VOO_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE ;

CREATE OR REPLACE TRIGGER  "BI_VOO" 
  before insert on "VOOS"              
  for each row 
begin  
  if :NEW."CODIGO" is null then
    select ('ADA-'||lpad("VOO_SEQ".nextval, 6, 0)) into :NEW."CODIGO" from dual;
  end if;
end;

ALTER TRIGGER  "BI_VOO" ENABLE;

alter table "VOOS" add
("VAGAS_ECON" NUMBER)
/   


alter table "VOOS" add
("VAGAS_EXEC" NUMBER)
/   

update "VOOS"  set VAGAS_ECON = 120;

update "VOOS"  set VAGAS_EXEC = 24;

alter table VOOS add ( "hr_destino" VARCHAR2(5), "hr_chegada" VARCHAR2(5),"duracao" VARCHAR2(5));
update "VOOS"  set hr_destino = '08:00';
update "VOOS"  set hr_chegada= '10:00';
update "VOOS"  set duracao= '2:00';